package co.fitstart.admin.dao;

import java.util.Date;
import java.util.List;
import java.util.Map;
import com.demo2do.core.persistence.annotation.Dynamic;
import com.demo2do.core.persistence.annotation.Limit;
import com.demo2do.core.persistence.annotation.Query;
import com.demo2do.core.persistence.annotation.Variable;
import co.fitstart.admin.entity.report.CoachFinishedScheduleStat;
import co.fitstart.admin.entity.report.FinishedCourseReportStatistics;
import co.fitstart.admin.entity.report.RevenueReportStatistics;
import co.fitstart.admin.entity.report.SiteFinishedScheduleStat;
import co.fitstart.entity.course.CourseType;
import co.fitstart.entity.course.round.CourseRound;
import co.fitstart.entity.course.round.Goal;
import co.fitstart.entity.course.round.RoundStatus;
import co.fitstart.entity.course.schedule.CourseSchedule;
import co.fitstart.entity.course.schedule.ScheduleStatus;
import co.fitstart.entity.courseware.fitnessgroup.CoursewareEditStatus;
import co.fitstart.entity.order.Order;
import co.fitstart.entity.order.OrderStatus;
import co.fitstart.entity.order.OrderType;
import co.fitstart.entity.order.PaymentStatus;
import co.fitstart.entity.principal.Principal;
import co.fitstart.entity.report.FinishedCourseReport;
import co.fitstart.entity.report.RevenueReport;
import co.fitstart.entity.report.RevenueType;
import co.fitstart.entity.report.VisitorReport;
import co.fitstart.entity.site.Site;
import co.fitstart.entity.user.Student;
import co.fitstart.entity.user.StudentTargetData;
import co.fitstart.entity.user.User;
import co.fitstart.entity.user.bwh.BwhRecord;
import co.fitstart.entity.user.inbody.InbodyRecord;
import co.fitstart.entity.user.investigation.FitInvestigation;
import co.fitstart.entity.user.investigation.HealthInvestigation;
import co.fitstart.entity.user.investigation.ParticipateInvestigation;

/**
 * 
 * @author dan
 *
 */
@Dynamic("hibernate")
public interface ReportDao {

    /**
     * 
     * @param startTime
     * @param endTime
     * @param beginIndex
     * @param maxResult
     * @return
     */
    @Limit
    @Query("FROM RevenueReport WHERE disabled = false AND occurTime BETWEEN :startTime AND :endTime ORDER BY site,occurTime ASC,salesman ASC")
    public List<RevenueReport> listRevenueReport(@Variable("startTime") Date startTime, @Variable("endTime") Date endTime, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);

    /**
     * 
     * @param site
     * @param startTime
     * @param endTime
     * @param beginIndex
     * @param maxResult
     * @return
     */
    @Limit
    @Query("FROM RevenueReport WHERE disabled = false AND site = :site AND occurTime BETWEEN :startTime AND :endTime ORDER BY site,occurTime ASC,salesman ASC")
    public List<RevenueReport> listRevenueReport(@Variable("site") Site site, @Variable("startTime") Date startTime, @Variable("endTime") Date endTime, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);

    /**
     * 
     * @param sites
     * @param startTime
     * @param endTime
     * @param beginIndex
     * @param maxResult
     * @return
     */
    @Limit
    @Query("FROM RevenueReport WHERE disabled = false AND site IN (:sites) AND occurTime BETWEEN :startTime AND :endTime ORDER BY site,occurTime ASC,salesman ASC")
    public List<RevenueReport> listRevenueReport(@Variable("sites") List<Site> sites, @Variable("startTime") Date startTime, @Variable("endTime") Date endTime, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);
    
    /**
     * 
     * @param sites
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("FROM RevenueReport WHERE disabled = false AND site IN (:sites) AND occurTime BETWEEN :startTime AND :endTime ORDER BY site,occurTime ASC,salesman ASC")
    public List<RevenueReport> listRevenueReport(@Variable("sites") List<Site> sites, @Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("FROM RevenueReport WHERE disabled = false AND occurTime BETWEEN :startTime AND :endTime ORDER BY site,occurTime ASC,salesman ASC")
    public List<RevenueReport> listRevenueReport(@Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @param site
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("FROM RevenueReport WHERE disabled = false AND site = :site AND occurTime BETWEEN :startTime AND :endTime ORDER BY site,occurTime ASC,salesman ASC")
    public List<RevenueReport> listRevenueReport(@Variable("site") Site site, @Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("SELECT new co.fitstart.admin.entity.report.RevenueReportStatistics(report.site.id, report.revenueType, report.course.id, COUNT(*), SUM(report.offer)) FROM RevenueReport report WHERE report.disabled = false AND report.occurTime BETWEEN :startTime AND :endTime GROUP BY report.site, report.course, report.revenueType")
    public List<RevenueReportStatistics> listRevenueReportStatistics(@Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @param site
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("SELECT new co.fitstart.admin.entity.report.RevenueReportStatistics(report.site.id, report.revenueType, report.course.id, COUNT(*), SUM(report.offer)) FROM RevenueReport report WHERE report.disabled = false AND report.site = :site AND report.occurTime BETWEEN :startTime AND :endTime GROUP BY report.site, report.course, report.revenueType")
    public List<RevenueReportStatistics> listRevenueReportStatistics(@Variable("site") Site site, @Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @param sites
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("SELECT new co.fitstart.admin.entity.report.RevenueReportStatistics(report.site.id, report.revenueType, report.course.id, COUNT(*), SUM(report.offer)) FROM RevenueReport report WHERE report.disabled = false AND report.site IN (:sites) AND report.occurTime BETWEEN :startTime AND :endTime GROUP BY report.site, report.course, report.revenueType")
    public List<RevenueReportStatistics> listRevenueReportStatistics(@Variable("sites") List<Site> sites, @Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @param courseRound
     * @return
     */
    @Query("FROM RevenueReport WHERE disabled = false AND courseRound = :courseRound")
    public RevenueReport loadRevenueReport(@Variable("courseRound") CourseRound courseRound);

    /**
     * 
     * @param order
     * @param type
     * @return
     */
    @Query("FROM RevenueReport WHERE disabled = false AND courseRound.order = :order and revenueType = :type")
    public RevenueReport loadRevenueReport(@Variable("order") Order order, @Variable("type") RevenueType type);

    /**
     * 
     * @param startTime
     * @param endTime
     * @param beginIndex
     * @param maxResult
     * @return
     */
    @Limit
    @Query("FROM FinishedCourseReport WHERE disabled = false AND occurTime BETWEEN :startTime AND :endTime ORDER BY site, occurTime")
    public List<FinishedCourseReport> listFinishedCourseReport(@Variable("startTime") Date startTime, @Variable("endTime") Date endTime, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);

    /**
     * 
     * @param site
     * @param startTime
     * @param endTime
     * @param beginIndex
     * @param maxResult
     * @return
     */
    @Limit
    @Query("FROM FinishedCourseReport WHERE disabled = false AND site = :site AND occurTime BETWEEN :startTime AND :endTime ORDER BY site, occurTime")
    public List<FinishedCourseReport> listFinishedCourseReport(@Variable("site") Site site, @Variable("startTime") Date startTime, @Variable("endTime") Date endTime, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);

    /**
     * 
     * @param site
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("FROM FinishedCourseReport WHERE disabled = false AND site = :site AND occurTime BETWEEN :startTime AND :endTime ORDER BY site, occurTime")
    public List<FinishedCourseReport> listFinishedCourseReport(@Variable("site") Site site, @Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("SELECT new co.fitstart.admin.entity.report.FinishedCourseReportStatistics(report.site.id, report.course.typeId, COUNT(*), SUM(report.offer)) FROM FinishedCourseReport report WHERE report.disabled = false AND report.occurTime BETWEEN :startTime AND :endTime GROUP BY report.site, report.course.typeId")
    public List<FinishedCourseReportStatistics> listFinishedCourseReportStatistics(@Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @param site
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("SELECT new co.fitstart.admin.entity.report.FinishedCourseReportStatistics(report.site.id, report.course.typeId, COUNT(*), SUM(report.offer)) FROM FinishedCourseReport report WHERE report.disabled = false AND report.site = :site AND report.occurTime BETWEEN :startTime AND :endTime GROUP BY report.site, report.course.typeId")
    public List<FinishedCourseReportStatistics> listFinishedCourseReportStatistics(@Variable("site") Site site, @Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @param sites
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("SELECT new co.fitstart.admin.entity.report.FinishedCourseReportStatistics(report.site.id, report.course.typeId, COUNT(*), SUM(report.offer)) FROM FinishedCourseReport report WHERE report.disabled = false AND report.site IN (:sites) AND report.occurTime BETWEEN :startTime AND :endTime GROUP BY report.site, report.course.typeId")
    public List<FinishedCourseReportStatistics> listFinishedCourseReportStatistics(@Variable("sites") List<Site> sites, @Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @param site
     * @param occurDate
     * @return
     */
    @Query("FROM VisitorReport WHERE site = :site AND occurDate = :occurDate")
    public VisitorReport loadVisitorReport(@Variable("site") Site site, @Variable("occurDate") Date occurDate);

    /**
     * 
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("SELECT new co.fitstart.entity.report.VisitorReport(SUM(report.visitAmount), SUM(report.experienceAmount), SUM(report.coachExperienceAmount), SUM(report.orderedAmount), report.site) FROM VisitorReport report WHERE report.occurDate BETWEEN :startTime AND :endTime GROUP BY report.site")
    public List<VisitorReport> listVisitorReport(@Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @param site
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("SELECT new co.fitstart.entity.report.VisitorReport(SUM(report.visitAmount), SUM(report.experienceAmount), SUM(report.coachExperienceAmount), SUM(report.orderedAmount), report.site) FROM VisitorReport report WHERE report.site = :site AND report.occurDate BETWEEN :startTime AND :endTime GROUP BY report.site")
    public List<VisitorReport> listVisitorReport(@Variable("site") Site site, @Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @param sites
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("SELECT new co.fitstart.entity.report.VisitorReport(SUM(report.visitAmount), SUM(report.experienceAmount), SUM(report.coachExperienceAmount), SUM(report.orderedAmount), report.site) FROM VisitorReport report WHERE report.site IN (:sites) AND report.occurDate BETWEEN :startTime AND :endTime GROUP BY report.site")
    public List<VisitorReport> listVisitorReport(@Variable("sites") List<Site> sites, @Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @param site
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("FROM VisitorReport WHERE site = :site AND occurDate BETWEEN :startTime AND :endTime")
    public List<VisitorReport> listVisitorReportDetail(@Variable("site") Site site, @Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @param site
     * @param beginDate
     * @param endDate
     * @return
     */
    @Query("SELECT COUNT(*) FROM VisitorRecord WHERE disabled = false AND site = :site AND occurDate BETWEEN :beginDate AND :endDate")
    public int getVisitorAmount(@Variable("site") Site site, @Variable("beginDate") Date beginDate, @Variable("endDate") Date endDate);

    /**
     * 
     * @param beginDate
     * @param endDate
     * @return
     */
    @Query("SELECT COUNT(*) FROM VisitorRecord WHERE disabled = false AND occurDate BETWEEN :beginDate AND :endDate")
    public int getVisitorAmount(@Variable("beginDate") Date beginDate, @Variable("endDate") Date endDate);

    /**
     * 
     * @param type
     * @param site
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("SELECT round.student FROM CourseRound round WHERE round.disabled = false AND round.courseType in (:type) AND round.site = :site AND round.student.disabled = false AND (round.student.stat.individualCount+round.student.stat.groupCount+round.student.stat.yearCount) > 0 AND round.student.createTime BETWEEN :startTime AND :endTime")
    public List<Student> listStudent(@Variable("type") CourseType[] type, @Variable("site") Site site, @Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @param type
     * @param sites
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("SELECT round.student FROM CourseRound round WHERE round.disabled = false AND round.courseType in (:type) AND round.site IN (:sites) AND round.student.disabled = false AND (round.student.stat.individualCount+round.student.stat.groupCount+round.student.stat.yearCount) > 0 AND round.student.createTime BETWEEN :startTime AND :endTime")
    public List<Student> listStudent(@Variable("type") CourseType[] type, @Variable("sites") List<Site> sites, @Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * @param student
     * @return
     */
    @Query("FROM CourseRound WHERE student in ( :st ) AND disabled = false")
    public List<CourseRound> listCourseRound(@Variable("st") List<Student> student);

    /**
     * @param students
     * @return
     */
    @Query("FROM CourseSchedule WHERE student in ( :st ) AND disabled = false AND occurDate <> NULL AND occurHour <> NULL")
    public List<CourseSchedule> listCourseSchedule(@Variable("st") List<Student> students);


    /**
     * @param student
     * @param createTime
     * @return
     */
    @Query("FROM CourseRound WHERE student = :st AND disabled = false ORDER BY createTime DESC")
    public List<CourseRound> getRecentCourseRound(@Variable("st") Student student);

    /**
     * 
     * @param students
     * @return
     */
    @Query("FROM CourseRound WHERE student in ( :students ) AND disabled = false ORDER BY createTime DESC")
    public List<CourseRound> listRecentCourseRound(@Variable("students") List<Student> students);

    /**
     * 
     * @param user
     * @return
     */
    @Query("FROM HealthInvestigation WHERE user = :user ORDER BY createTime DESC")
    public List<HealthInvestigation> getRecentHealthInvestigation(@Variable("user") User user);

    /**
     * 
     * @param users
     * @return
     */
    @Query("FROM HealthInvestigation WHERE user in ( :users ) ORDER BY createTime DESC")
    public List<HealthInvestigation> listRecentHealthInvestigation(@Variable("users") List<User> users);

    /**
     * 
     * @param user
     * @return
     */
    @Query("FROM ParticipateInvestigation WHERE user = :user ORDER BY createTime DESC")
    public List<ParticipateInvestigation> getRecentParticipateInvestigation(@Variable("user") User user);

    /**
     * 
     * @param users
     * @return
     */
    @Query("FROM ParticipateInvestigation WHERE user in ( :users ) ORDER BY createTime DESC")
    public List<ParticipateInvestigation> listRecentParticipateInvestigation(@Variable("users") List<User> users);

    /**
     * 
     * @param user
     * @return
     */
    @Query("FROM FitInvestigation WHERE user = :user ORDER BY createTime DESC")
    public List<FitInvestigation> getRecentFitInvestigation(@Variable("user") User user);

    /**
     * 
     * @param users
     * @return
     */
    @Query("FROM FitInvestigation WHERE user in ( :users ) ORDER BY createTime DESC")
    public List<FitInvestigation> listRecentFitInvestigation(@Variable("users") List<User> users);

    /**
     * 
     * @param user
     * @return
     */
    @Query("FROM InbodyRecord WHERE user = :user AND disabled = false ORDER BY recordTime DESC")
    public List<InbodyRecord> getInbodyRecord(@Variable("user") User user);

    /**
     * 
     * @param users
     * @return
     */
    @Query("FROM InbodyRecord WHERE user in ( :users ) AND disabled = false ORDER BY recordTime DESC")
    public List<InbodyRecord> listInbodyRecord(@Variable("users") List<User> users);

    /**
     * 
     * @param user
     * @return
     */
    @Query("FROM BwhRecord WHERE user = :user AND disabled = false ORDER BY recordTime DESC")
    public List<BwhRecord> getBwhRecord(@Variable("user") User user);

    /**
     * 
     * @param users
     * @return
     */
    @Query("FROM BwhRecord WHERE user in ( :users ) AND disabled = false ORDER BY recordTime DESC")
    public List<BwhRecord> listBwhRecord(@Variable("users") List<User> users);

    /**
     * 
     * @param student
     * @param roundStatus
     * @return
     */
    @Query("FROM CourseRound WHERE courseStatus = :roundStatus AND disabled = false AND student in ( :st ) ")
    public List<CourseRound> listCourseRound(@Variable("st") List<Student> student, @Variable("roundStatus") RoundStatus roundStatus);

    /**
     * 
     * @return
     */
    @Query("FROM Student student WHERE student.disabled = false AND student.stat.onlineCount > 0 AND student.createTime BETWEEN :startTime AND :endTime")
    public List<Student> listOnlineStudent(@Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @return
     */
    @Query("FROM Student student WHERE student.disabled = false AND (student.stat.individualCount+student.stat.groupCount+student.stat.yearCount) > 0 AND student.createTime BETWEEN :startTime AND :endTime")
    public List<Student> listOfflineStudent(@Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @param startTime
     * @param endTime
     * @return
     */
    
    @Query("SELECT new co.fitstart.admin.entity.report.CoachFinishedScheduleStat(site.name,principal.name,courseDescription,COUNT(id)) FROM CoachSigninRecord WHERE ignored = false AND courseDay BETWEEN :startTime AND :endTime GROUP BY site,principal,courseDescription ORDER BY site,principal,courseDescription")
    public List<CoachFinishedScheduleStat> listCoachFinishedScheduleStat(@Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @param site
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("SELECT new co.fitstart.admin.entity.report.CoachFinishedScheduleStat(site.name,principal.name,courseDescription,COUNT(id)) FROM CoachSigninRecord WHERE ignored = false AND courseDay BETWEEN :startTime AND :endTime AND site = :site GROUP BY site,principal,courseDescription ORDER BY site,principal,courseDescription")
    public List<CoachFinishedScheduleStat> listCoachFinishedScheduleStat(@Variable("site") Site site, @Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @param sites
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("SELECT new co.fitstart.admin.entity.report.CoachFinishedScheduleStat(site.name,principal.name,courseDescription,COUNT(id)) FROM CoachSigninRecord WHERE ignored = false AND courseDay BETWEEN :startTime AND :endTime AND site IN (:sites) GROUP BY site,principal,courseDescription ORDER BY site,principal,courseDescription")
    public List<CoachFinishedScheduleStat> listCoachFinishedScheduleStat(@Variable("sites") List<Site> sites, @Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("SELECT new co.fitstart.admin.entity.report.SiteFinishedScheduleStat(site.id,COUNT(id)) FROM CoachSigninRecord WHERE ignored = false AND courseDay BETWEEN :startTime AND :endTime GROUP BY site")
    public List<SiteFinishedScheduleStat> listSiteFinishedScheduleStat(@Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @param sites
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("SELECT new co.fitstart.admin.entity.report.SiteFinishedScheduleStat(site.id,COUNT(id)) FROM CoachSigninRecord WHERE ignored = false AND site IN (:sites) AND courseDay BETWEEN :startTime AND :endTime GROUP BY site")
    public List<SiteFinishedScheduleStat> listSiteFinishedScheduleStat(@Variable("sites") List<Site> sites, @Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @param site
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("SELECT COUNT(id) FROM CoachSigninRecord WHERE ignored = false AND site = :site AND courseDay BETWEEN :startTime AND :endTime")
    public int countSiteFinishedSchedule(@Variable("site") Site site, @Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("SELECT COUNT(*) FROM FinishedCourseReport WHERE disabled = false AND occurTime BETWEEN :startTime AND :endTime")
    public int countFinishedCourseReport(@Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @param site
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("SELECT COUNT(*) FROM FinishedCourseReport WHERE disabled = false AND site = :site AND occurTime BETWEEN :startTime AND :endTime")
    public int countFinishedCourseReport(@Variable("site") Site site, @Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @param sites
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("SELECT COUNT(*) FROM FinishedCourseReport WHERE disabled = false AND site IN (:sites) AND occurTime BETWEEN :startTime AND :endTime")
    public int countFinishedCourseReport(@Variable("sites") List<Site> sites, @Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("SELECT SUM(offer) FROM FinishedCourseReport WHERE disabled = false AND occurTime BETWEEN :startTime AND :endTime")
    public int sumFinishedCourseReportOffer(@Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @param site
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("SELECT SUM(offer) FROM FinishedCourseReport WHERE disabled = false AND site = :site AND occurTime BETWEEN :startTime AND :endTime")
    public int sumFinishedCourseReportOffer(@Variable("site") Site site, @Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    /**
     * 
     * @param sites
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("SELECT SUM(offer) FROM FinishedCourseReport WHERE disabled = false AND site IN (:sites) AND occurTime BETWEEN :startTime AND :endTime")
    public int sumFinishedCourseReportOffer(@Variable("sites") List<Site> sites, @Variable("startTime") Date startTime, @Variable("endTime") Date endTime);

    @Query("SELECT new Map(batch.courseRound.id AS id,MAX(batch.sorting) AS week) FROM Dietary" + " WHERE disabled=false AND batch.disabled=false AND batch.courseRound.disabled=false AND batch.editStatus = :editStatus" + " AND batch.courseRound.site IN (:sites) AND batch.courseRound.courseStatus IN (:courseStatus)" + " AND ((breakfastDescription IS NOT NULL AND breakfastDescription !='')" + " OR (breakfastSnackDescription IS NOT NULL AND breakfastSnackDescription !='')"
            + " OR (lunchDescription IS NOT NULL AND lunchDescription !='')" + " OR (lunchSnackDescription IS NOT NULL AND lunchSnackDescription !='')" + " OR (dinnerDescription IS NOT NULL AND dinnerDescription !='')" + " OR (dinnerSnackDescription IS NOT NULL AND dinnerSnackDescription != '')" + " OR (waterIntake IS NOT NULL AND waterIntake != null)" + " OR (remark IS NOT NULL AND remark !='')) GROUP BY batch.courseRound.id")
    public List<Map<String, Object>> listDietary(@Variable("editStatus") CoursewareEditStatus editStatus, @Variable("sites") List<Site> sites, @Variable("courseStatus") List<RoundStatus> courseStatus);

    @Query("SELECT new Map(batch.courseRound.id AS id,MAX(batch.sorting) AS week) FROM Dietary" + " WHERE disabled=false AND batch.disabled=false AND batch.courseRound.disabled=false AND batch.editStatus = :editStatus" + " AND batch.courseRound.site IN (:sites) AND batch.courseRound.courseStatus IN (:courseStatus) AND batch.courseRound.coach = :coach" + " AND ((breakfastDescription IS NOT NULL AND breakfastDescription !='')"
            + " OR (breakfastSnackDescription IS NOT NULL AND breakfastSnackDescription !='')" + " OR (lunchDescription IS NOT NULL AND lunchDescription !='')" + " OR (lunchSnackDescription IS NOT NULL AND lunchSnackDescription !='')" + " OR (dinnerDescription IS NOT NULL AND dinnerDescription !='')" + " OR (dinnerSnackDescription IS NOT NULL AND dinnerSnackDescription != '')" + " OR (waterIntake IS NOT NULL AND waterIntake != null)"
            + " OR (remark IS NOT NULL AND remark !='')) GROUP BY batch.courseRound.id")
    public List<Map<String, Object>> listDietary(@Variable("editStatus") CoursewareEditStatus editStatus, @Variable("sites") List<Site> sites, @Variable("courseStatus") List<RoundStatus> courseStatus, @Variable("coach") Principal coach);

    /**
     * 
     * @param begin
     * @param end
     * @param courseTypes
     * @param courseStatuss
     * @return
     */
    @Query("SELECT new Map(courseSchedule.courseRound.id AS id, COUNT(id) AS total) FROM CourseScheduleCancelRecord WHERE occurDate BETWEEN :begin AND :end AND courseSchedule.courseRound.disabled = false AND courseSchedule.courseType IN (:courseTypes) AND courseSchedule.course.experience = false AND courseSchedule.courseRound.courseStatus IN (:courseStatuss) AND courseSchedule.courseRound.site IN (:sites) GROUP BY courseSchedule.courseRound.id")
    public List<Map<String, Object>> listScheduleCancelStat(@Variable("begin") Date begin, @Variable("end") Date end, @Variable("courseTypes") List<CourseType> courseTypes, @Variable("courseStatuss") List<RoundStatus> courseStatuss, @Variable("sites") List<Site> sites);

    /**
     * 
     * @param begin
     * @param end
     * @param courseTypes
     * @param courseStatuss
     * @param coach
     * @return
     */
    @Query("SELECT new Map(courseSchedule.courseRound.id AS id, COUNT(id) AS total) FROM CourseScheduleCancelRecord WHERE occurDate BETWEEN :begin AND :end AND courseSchedule.courseRound.disabled = false AND courseSchedule.courseType IN (:courseTypes) AND courseSchedule.course.experience = false AND courseSchedule.courseRound.courseStatus IN (:courseStatuss) AND courseSchedule.courseRound.site IN (:sites) AND courseSchedule.courseRound.coach = :coach GROUP BY courseSchedule.courseRound.id")
    public List<Map<String, Object>> listScheduleCancelStat(@Variable("begin") Date begin, @Variable("end") Date end, @Variable("courseTypes") List<CourseType> courseTypes, @Variable("courseStatuss") List<RoundStatus> courseStatuss, @Variable("sites") List<Site> sites, @Variable("coach") Principal coach);

    /**
     * 
     * @param scheduleStatus
     * @param courseTypes
     * @param courseStatuss
     * @param sites
     * @return
     */
    @Query("SELECT new Map(courseRound.id AS id, MAX(occurDate) AS occurDate) FROM CourseSchedule WHERE disabled = false AND scheduleStatus = :scheduleStatus AND courseRound.disabled = false AND courseType IN (:courseTypes) AND course.experience = false AND courseRound.courseStatus IN (:courseStatuss) AND courseRound.site IN (:sites) GROUP BY courseRound")
    public List<Map<String, Object>> listLatestScheduleTime(@Variable("scheduleStatus") ScheduleStatus scheduleStatus, @Variable("courseTypes") List<CourseType> courseTypes, @Variable("courseStatuss") List<RoundStatus> courseStatuss, @Variable("sites") List<Site> sites);

    /**
     * 
     * @param scheduleStatus
     * @param courseTypes
     * @param courseStatuss
     * @param sites
     * @param coach
     * @return
     */
    @Query("SELECT new Map(courseRound.id AS id, MAX(occurDate) AS occurDate) FROM CourseSchedule WHERE disabled = false AND scheduleStatus = :scheduleStatus AND courseRound.disabled = false AND courseType IN (:courseTypes) AND course.experience = false AND courseRound.courseStatus IN (:courseStatuss) AND courseRound.site IN (:sites) AND courseRound.coach = :coach GROUP BY courseRound")
    public List<Map<String, Object>> listLatestScheduleTime(@Variable("scheduleStatus") ScheduleStatus scheduleStatus, @Variable("courseTypes") List<CourseType> courseTypes, @Variable("courseStatuss") List<RoundStatus> courseStatuss, @Variable("sites") List<Site> sites, @Variable("coach") Principal coach);

    /**
     * 
     * @param courseTypes
     * @param courseStatus
     * @param begin
     * @param end
     * @return
     */
    @Query("SELECT courseRound.student.user FROM CourseRound courseRound WHERE courseRound.disabled = false AND courseRound.courseType IN (:courseTypes) AND courseRound.course.experience = false AND courseRound.courseStatus = :courseStatus AND courseRound.finishTime BETWEEN :begin AND :end")
    public List<User> listUser(@Variable("courseTypes") List<CourseType> courseTypes, @Variable("courseStatus") RoundStatus courseStatus, @Variable("begin") Date begin, @Variable("end") Date end);

    /**
     * 
     * @param courseTypes
     * @param courseStatus
     * @param begin
     * @param end
     * @param site
     * @return
     */
    @Query("SELECT courseRound.student.user FROM CourseRound courseRound WHERE courseRound.disabled = false AND courseRound.courseType IN (:courseTypes) AND courseRound.course.experience = false AND courseRound.courseStatus = :courseStatus AND courseRound.finishTime BETWEEN :begin AND :end AND courseRound.site = :site")
    public List<User> listUser(@Variable("courseTypes") List<CourseType> courseTypes, @Variable("courseStatus") RoundStatus courseStatus, @Variable("begin") Date begin, @Variable("end") Date end, @Variable("site") Site site);

    /**
     * 
     * @param courseTypes
     * @param courseStatus
     * @param begin
     * @param end
     * @param sites
     * @return
     */
    @Query("SELECT courseRound.student.user FROM CourseRound courseRound WHERE courseRound.disabled = false AND courseRound.courseType IN (:courseTypes) AND courseRound.course.experience = false AND courseRound.courseStatus = :courseStatus AND courseRound.finishTime BETWEEN :begin AND :end AND courseRound.site IN (:sites)")
    public List<User> listUser(@Variable("courseTypes") List<CourseType> courseTypes, @Variable("courseStatus") RoundStatus courseStatus, @Variable("begin") Date begin, @Variable("end") Date end, @Variable("sites") List<Site> sites);

    /**
     * 
     * @param orderTypes
     * @param orderStatuss
     * @param paymentStatus
     * @param users
     * @return
     */
    @Query("SELECT new Map(user.id AS id, COUNT(id) AS total) FROM Order WHERE disabled = false AND course.experience = false AND orderType IN (:orderTypes) AND orderStatus IN (:orderStatuss) AND paymentStatus = :paymentStatus AND user IN (:users) GROUP BY user HAVING COUNT(id) > 1")
    public List<Map<String, Object>> countUserOrder(@Variable("orderTypes") List<OrderType> orderTypes, @Variable("orderStatuss") List<OrderStatus> orderStatuss, @Variable("paymentStatus") PaymentStatus paymentStatus, @Variable("users") List<User> users);

    /**
     * 
     * @param courseTypes
     * @param courseStatus
     * @param begin
     * @param end
     * @return
     */
    @Query("SELECT new Map(site.id AS id, COUNT(DISTINCT student) AS total) FROM CourseRound WHERE disabled = false AND course.experience = false AND courseType IN (:courseTypes) AND courseStatus = :courseStatus AND finishTime BETWEEN :begin AND :end GROUP BY site")
    public List<Map<String, Object>> countFinishedStudent(@Variable("courseTypes") List<CourseType> courseTypes, @Variable("courseStatus") RoundStatus courseStatus, @Variable("begin") Date begin, @Variable("end") Date end);

    /**
     * 
     * @param courseTypes
     * @param courseStatus
     * @param begin
     * @param end
     * @return
     */
    @Query("SELECT COUNT(DISTINCT student) FROM CourseRound WHERE disabled = false AND course.experience = false AND courseType IN (:courseTypes) AND courseStatus = :courseStatus AND finishTime BETWEEN :begin AND :end")
    public int countAllFinishedStudent(@Variable("courseTypes") List<CourseType> courseTypes, @Variable("courseStatus") RoundStatus courseStatus, @Variable("begin") Date begin, @Variable("end") Date end);

    /**
     * 
     * @param courseTypes
     * @param courseStatus
     * @param begin
     * @param end
     * @param site
     * @return
     */
    @Query("SELECT COUNT(DISTINCT student) FROM CourseRound WHERE disabled = false AND course.experience = false AND courseType IN (:courseTypes) AND courseStatus = :courseStatus AND finishTime BETWEEN :begin AND :end AND site = :site")
    public int countFinishedStudent(@Variable("courseTypes") List<CourseType> courseTypes, @Variable("courseStatus") RoundStatus courseStatus, @Variable("begin") Date begin, @Variable("end") Date end, @Variable("site") Site site);

    /**
     * 
     * @param courseTypes
     * @param courseStatus
     * @param begin
     * @param end
     * @param sites
     * @return
     */
    @Query("SELECT new Map(site.id AS id, COUNT(DISTINCT student) AS total) FROM CourseRound WHERE disabled = false AND course.experience = false AND courseType IN (:courseTypes) AND courseStatus = :courseStatus AND finishTime BETWEEN :begin AND :end AND site IN (:sites) GROUP BY site")
    public List<Map<String, Object>> countFinishedStudent(@Variable("courseTypes") List<CourseType> courseTypes, @Variable("courseStatus") RoundStatus courseStatus, @Variable("begin") Date begin, @Variable("end") Date end, @Variable("sites") List<Site> sites);

    /**
     * 
     * @param courseTypes
     * @param courseStatus
     * @param begin
     * @param end
     * @param sites
     * @return
     */
    @Query("SELECT COUNT(DISTINCT student) FROM CourseRound WHERE disabled = false AND course.experience = false AND courseType IN (:courseTypes) AND courseStatus = :courseStatus AND finishTime BETWEEN :begin AND :end AND site IN (:sites)")
    public int countAllFinishedStudent(@Variable("courseTypes") List<CourseType> courseTypes, @Variable("courseStatus") RoundStatus courseStatus, @Variable("begin") Date begin, @Variable("end") Date end, @Variable("sites") List<Site> sites);

    /**
     * 
     * @param courseTypes
     * @param courseStatus
     * @param begin
     * @param end
     * @return
     */
    @Query("SELECT goal FROM Goal goal WHERE goal.courseRound.disabled = false AND goal.courseRound.course.experience = false AND goal.courseRound.courseType IN (:courseTypes) AND goal.courseRound.courseStatus = :courseStatus AND goal.courseRound.finishTime BETWEEN :begin AND :end")
    public List<Goal> listGoal(@Variable("courseTypes") List<CourseType> courseTypes, @Variable("courseStatus") RoundStatus courseStatus, @Variable("begin") Date begin, @Variable("end") Date end);

    /**
     * 
     * @param courseTypes
     * @param courseStatus
     * @param begin
     * @param end
     * @param site
     * @return
     */
    @Query("SELECT goal FROM Goal goal WHERE goal.courseRound.disabled = false AND goal.courseRound.course.experience = false AND goal.courseRound.courseType IN (:courseTypes) AND goal.courseRound.courseStatus = :courseStatus AND goal.courseRound.finishTime BETWEEN :begin AND :end AND goal.courseRound.site=:site")
    public List<Goal> listGoal(@Variable("courseTypes") List<CourseType> courseTypes, @Variable("courseStatus") RoundStatus courseStatus, @Variable("begin") Date begin, @Variable("end") Date end, @Variable("site") Site site);

    /**
     * 
     * @param courseTypes
     * @param courseStatus
     * @param begin
     * @param end
     * @param sites
     * @return
     */
    @Query("SELECT goal FROM Goal goal WHERE goal.courseRound.disabled = false AND goal.courseRound.course.experience = false AND goal.courseRound.courseType IN (:courseTypes) AND goal.courseRound.courseStatus = :courseStatus AND goal.courseRound.finishTime BETWEEN :begin AND :end AND goal.courseRound.site IN (:sites)")
    public List<Goal> listGoal(@Variable("courseTypes") List<CourseType> courseTypes, @Variable("courseStatus") RoundStatus courseStatus, @Variable("begin") Date begin, @Variable("end") Date end, @Variable("sites") List<Site> sites);

    /**
     * 
     * @param courseTypes
     * @param courseStatuss
     * @param sites
     * @return
     */
    @Query("SELECT new Map(courseRound.id AS id, previousPicture AS before, latterPicture AS after) FROM ResultReport WHERE disabled = false AND courseRound.disabled = false AND courseRound.courseType IN (:courseTypes) AND courseRound.course.experience = false AND courseRound.courseStatus IN (:courseStatuss) AND courseRound.site IN (:sites)")
    public List<Map<String, Object>> listContrastPhoto(@Variable("courseTypes") List<CourseType> courseTypes, @Variable("courseStatuss") List<RoundStatus> courseStatuss, @Variable("sites") List<Site> sites);

    /**
     * 
     * @param courseTypes
     * @param courseStatuss
     * @param sites
     * @param coach
     * @return
     */
    @Query("SELECT new Map(courseRound.id AS id, previousPicture AS before, latterPicture AS after) FROM ResultReport WHERE disabled = false AND courseRound.disabled = false AND courseRound.courseType IN (:courseTypes) AND courseRound.course.experience = false AND courseRound.courseStatus IN (:courseStatuss) AND courseRound.site IN (:sites) AND courseRound.coach = :coach")
    public List<Map<String, Object>> listContrastPhoto(@Variable("courseTypes") List<CourseType> courseTypes, @Variable("courseStatuss") List<RoundStatus> courseStatuss, @Variable("sites") List<Site> sites, @Variable("coach") Principal coach);

    /**
     * 
     * @param courseTypes
     * @param courseStatus
     * @param begin
     * @param end
     * @return
     */
    @Query("SELECT new Map(courseRound.id AS id, previousPicture AS before, latterPicture AS after) FROM ResultReport WHERE disabled = false AND courseRound.disabled = false AND courseRound.courseType IN (:courseTypes) AND courseRound.course.experience = false AND courseRound.courseStatus = :courseStatus AND courseRound.finishTime BETWEEN :begin AND :end")
    public List<Map<String, Object>> listContrastPhoto(@Variable("courseTypes") List<CourseType> courseTypes, @Variable("courseStatus") RoundStatus courseStatus, @Variable("begin") Date begin, @Variable("end") Date end);

    /**
     * 
     * @param courseTypes
     * @param courseStatus
     * @param begin
     * @param end
     * @param site
     * @return
     */
    @Query("SELECT new Map(courseRound.id AS id, previousPicture AS before, latterPicture AS after) FROM ResultReport WHERE disabled = false AND courseRound.disabled = false AND courseRound.courseType IN (:courseTypes) AND courseRound.course.experience = false AND courseRound.courseStatus = :courseStatus AND courseRound.finishTime BETWEEN :begin AND :end AND courseRound.site = :site")
    public List<Map<String, Object>> listContrastPhoto(@Variable("courseTypes") List<CourseType> courseTypes, @Variable("courseStatus") RoundStatus courseStatus, @Variable("begin") Date begin, @Variable("end") Date end, @Variable("site") Site site);

    /**
     * 
     * @param courseTypes
     * @param courseStatus
     * @param begin
     * @param end
     * @param sites
     * @return
     */
    @Query("SELECT new Map(courseRound.id AS id, previousPicture AS before, latterPicture AS after) FROM ResultReport WHERE disabled = false AND courseRound.disabled = false AND courseRound.courseType IN (:courseTypes) AND courseRound.course.experience = false AND courseRound.courseStatus = :courseStatus AND courseRound.finishTime BETWEEN :begin AND :end AND courseRound.site IN (:sites)")
    public List<Map<String, Object>> listContrastPhoto(@Variable("courseTypes") List<CourseType> courseTypes, @Variable("courseStatus") RoundStatus courseStatus, @Variable("begin") Date begin, @Variable("end") Date end, @Variable("sites") List<Site> sites);

    /**
     * 
     * @param courseTypes
     * @param courseStatus
     * @param begin
     * @param end
     * @return
     */
    @Query("FROM StudentTargetData WHERE disabled=false AND targetType IS NOT NULL AND courseRound.disabled = false AND courseRound.courseType IN (:courseTypes) AND courseRound.course.experience = false AND courseRound.courseStatus = :courseStatus AND courseRound.finishTime BETWEEN :begin AND :end")
    public List<StudentTargetData> listStudentTargetData(@Variable("courseTypes") List<CourseType> courseTypes, @Variable("courseStatus") RoundStatus courseStatus, @Variable("begin") Date begin, @Variable("end") Date end);

    /**
     * 
     * @param courseTypes
     * @param courseStatus
     * @param begin
     * @param end
     * @param site
     * @return
     */
    @Query("FROM StudentTargetData WHERE disabled=false AND targetType IS NOT NULL AND courseRound.disabled = false AND courseRound.courseType IN (:courseTypes) AND courseRound.course.experience = false AND courseRound.courseStatus = :courseStatus AND courseRound.finishTime BETWEEN :begin AND :end AND courseRound.site = :site")
    public List<StudentTargetData> listStudentTargetData(@Variable("courseTypes") List<CourseType> courseTypes, @Variable("courseStatus") RoundStatus courseStatus, @Variable("begin") Date begin, @Variable("end") Date end, @Variable("site") Site site);

    /**
     * 
     * @param courseTypes
     * @param courseStatus
     * @param begin
     * @param end
     * @param sites
     * @return
     */
    @Query("FROM StudentTargetData WHERE disabled=false AND targetType IS NOT NULL AND courseRound.disabled = false AND courseRound.courseType IN (:courseTypes) AND courseRound.course.experience = false AND courseRound.courseStatus = :courseStatus AND courseRound.finishTime BETWEEN :begin AND :end AND courseRound.site IN (:sites)")
    public List<StudentTargetData> listStudentTargetData(@Variable("courseTypes") List<CourseType> courseTypes, @Variable("courseStatus") RoundStatus courseStatus, @Variable("begin") Date begin, @Variable("end") Date end, @Variable("sites") List<Site> sites);

    /**
     * 
     * @param courseTypes
     * @param courseStatuss
     * @param sites
     * @return
     */
    @Query("FROM StudentTargetData WHERE disabled=false AND targetType IS NOT NULL AND courseRound.disabled=false AND courseRound.courseType IN (:courseTypes) AND courseRound.course.experience = false AND courseRound.courseStatus IN (:courseStatuss) AND courseRound.site IN (:sites)")
    public List<StudentTargetData> listStudentTargetData(@Variable("courseTypes") List<CourseType> courseTypes, @Variable("courseStatuss") List<RoundStatus> courseStatuss, @Variable("sites") List<Site> sites);

    /**
     * 
     * @param courseTypes
     * @param courseStatuss
     * @param sites
     * @param coach
     * @return
     */
    @Query("FROM StudentTargetData WHERE disabled=false AND targetType IS NOT NULL AND courseRound.disabled=false AND courseRound.courseType IN (:courseTypes) AND courseRound.course.experience = false AND courseRound.courseStatus IN (:courseStatuss) AND courseRound.site IN (:sites) AND courseRound.coach = :coach")
    public List<StudentTargetData> listStudentTargetData(@Variable("courseTypes") List<CourseType> courseTypes, @Variable("courseStatuss") List<RoundStatus> courseStatuss, @Variable("sites") List<Site> sites, @Variable("coach") Principal coach);
}
